package marina.Services;

//ctrl+shift+o 누르면 자동 import
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;

import marina.interfaces.J24ProfessorDAO;
import marina.model.J24ProfessorVO;

public class J24ProfessorDAOImpl implements J24ProfessorDAO {

	private static String DRV = "oracle.jdbc.driver.OracleDriver";
	private static String URL = "jdbc:oracle:thin:marina33/marina33@112.216.253.124:1521:xe";
	private static String insertSQL = " insert into professor(pfno,pfname,fields,skills,regdate) values (pfno.nextval,?,?,?,sysdate) ";
	private static String selectSQL = " select * from professor order by pfno ";
	private static String selectOneSQL = " select * from professor where pfname = ? order by pfno ";
	private static String updateSQL = "";
	private static String deleteSQL = "";
	String result = "";

	@Override
	public String insertProfessor(J24ProfessorVO pvo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = makeConn();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, pvo.getPfname());
			pstmt.setString(2, pvo.getFields());
			pstmt.setString(3, pvo.getSkills());
			int cnt = pstmt.executeUpdate();
			if (cnt > 0) {
				result = cnt + "건의 데이터가 입력됨.";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(conn, pstmt);
		}
		return result;
	}

	@Override
	public ArrayList<J24ProfessorVO> selectProfessor() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<J24ProfessorVO> pvoes = null;
		try {
			conn = makeConn();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			pvoes = new ArrayList<>();
			while (rs.next()) {
				J24ProfessorVO vo = new J24ProfessorVO(null, null, null);
				vo.setPfno(rs.getString(1));
				vo.setPfname(rs.getString(2));
				vo.setFields(rs.getString(3));
				vo.setSkills(rs.getString(4));
				vo.setRegdate(rs.getString(5));
				pvoes.add(vo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(conn, pstmt, rs);
		}
		return pvoes;
	}

	@Override
	public ArrayList<J24ProfessorVO> selectOneProfessor(String pfno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<J24ProfessorVO> pvooes = null;
		Scanner sc = new Scanner(System.in);
		String name = sc.next();
		try {
			J24ProfessorVO pvo = new J24ProfessorVO(null, null, null);
			conn = makeConn();
			pstmt = conn.prepareStatement(selectOneSQL);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			pvooes = new ArrayList<>();
			while(rs.next()) {
			pvo.setPfno(rs.getString(1));
			pvo.setPfname(rs.getString(2));
			pvo.setFields(rs.getString(3));
			pvo.setSkills(rs.getString(4));
			pvo.setRegdate(rs.getString(5));
			pvooes.add(pvo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(conn, pstmt, rs);
		}
		return pvooes;
	}

	@Override
	public String updateProfessor(String pfno) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String deleteProfessor(String pfno) {
		// TODO Auto-generated method stub
		return null;
	}

	private Connection makeConn() throws Exception {
		Connection conn = null;
		Class.forName(DRV);
		conn = DriverManager.getConnection(URL);
		return conn;
	}

	private void closeConn(Connection c, PreparedStatement p, ResultSet r) {
		if (r != null) {
			try {
				r.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (p != null) {
			try {
				p.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (c != null) {
			try {
				c.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private void closeConn(Connection c, PreparedStatement p) {
		if (p != null) {
			try {
				p.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (c != null) {
			try {
				c.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

}
